﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Sunboxsoft.Common;
using System.Data.SQLite;

namespace Sunboxsoft.ASMS.Client.Domain.LocalService
{
    public class CreateService
    {
        public ServiceResult CreateMasterData(string code, string name, string pinyin, string letter, int changeset)
        {
            using (SQLiteConnection connection = new SQLiteConnection(SQLiteDatabase.ConnectionString))
            {
                try
                {
                    connection.Open();
                }
                catch (Exception ex)
                {
                    return new ServiceResult(1, ex.ToString());
                }

                SQLiteCommand command = new SQLiteCommand(connection);
                command.CommandText = "insert into [ASMS_MasterData] ([Code],[Name],[Pinyin],[Letter],[Changeset]) values (@Code,@Name,@Pinyin,@Letter,@Changeset)";
                SQLiteParameter pCode = command.CreateParameter();
                pCode.ParameterName = "@Code";
                pCode.Value = code;
                command.Parameters.Add(pCode);

                SQLiteParameter pName = command.CreateParameter();
                pName.ParameterName = "@Name";
                pName.Value = name;
                command.Parameters.Add(pName);

                SQLiteParameter pPinyin = command.CreateParameter();
                pPinyin.ParameterName = "@Pinyin";
                pPinyin.Value = pinyin;
                command.Parameters.Add(pPinyin);

                SQLiteParameter pLetter = command.CreateParameter();
                pLetter.ParameterName = "@Letter";
                pLetter.Value = letter;
                command.Parameters.Add(pLetter);

                SQLiteParameter pChangeset = command.CreateParameter();
                pChangeset.ParameterName = "@Changeset";
                pChangeset.Value = changeset;
                command.Parameters.Add(pChangeset);

                try
                {
                    command.ExecuteNonQuery();
                    return new ServiceResult();
                }
                catch (Exception ex)
                {
                    return new ServiceResult(1, ex.ToString());
                }
            }
        }

        public ServiceResult CreateMasterData(SQLiteConnection connection, string code, string name, string pinyin, string letter, int changeset)
        {
            SQLiteCommand command = new SQLiteCommand(connection);
            command.CommandText = "insert into [ASMS_MasterData] ([Code],[Name],[Pinyin],[Letter],[Changeset]) values (@Code,@Name,@Pinyin,@Letter,@Changeset)";
            SQLiteParameter pCode = command.CreateParameter();
            pCode.ParameterName = "@Code";
            pCode.Value = code;
            command.Parameters.Add(pCode);

            SQLiteParameter pName = command.CreateParameter();
            pName.ParameterName = "@Name";
            pName.Value = name;
            command.Parameters.Add(pName);

            SQLiteParameter pPinyin = command.CreateParameter();
            pPinyin.ParameterName = "@Pinyin";
            pPinyin.Value = pinyin;
            command.Parameters.Add(pPinyin);

            SQLiteParameter pLetter = command.CreateParameter();
            pLetter.ParameterName = "@Letter";
            pLetter.Value = letter;
            command.Parameters.Add(pLetter);

            SQLiteParameter pChangeset = command.CreateParameter();
            pChangeset.ParameterName = "@Changeset";
            pChangeset.Value = changeset;
            command.Parameters.Add(pChangeset);

            try
            {
                command.ExecuteNonQuery();
                return new ServiceResult();
            }
            catch (Exception ex)
            {
                return new ServiceResult(1, ex.ToString());
            }
        }

        public ServiceResult CreateMasterDataItem(string masterDataCode, string code, string parentCode, string name, string pinyin, string letter, int sortIndex, int? tag)
        {
            using (SQLiteConnection connection = new SQLiteConnection(SQLiteDatabase.ConnectionString))
            {
                try
                {
                    connection.Open();
                }
                catch (Exception ex)
                {
                    return new ServiceResult(1, ex.ToString());
                }

                SQLiteCommand command = new SQLiteCommand(connection);
                command.CommandText = "insert into [ASMS_MasterDataItem] ([MasterDataCode],[Code],[ParentCode],[Name],[Pinyin],[Letter],[SortIndex],[Tag]) values (@MasterDataCode,@Code,@ParentCode,@Name,@Pinyin,@Letter,@SortIndex,@Tag)";

                SQLiteParameter pMDCode = command.CreateParameter();
                pMDCode.ParameterName = "@MasterDataCode";
                pMDCode.Value = masterDataCode;
                command.Parameters.Add(pMDCode);

                SQLiteParameter pCode = command.CreateParameter();
                pCode.ParameterName = "@Code";
                pCode.Value = code;
                command.Parameters.Add(pCode);

                SQLiteParameter pParentCode = command.CreateParameter();
                pParentCode.ParameterName = "@ParentCode";
                if (string.IsNullOrEmpty(parentCode))
                {
                    pParentCode.Value = DBNull.Value;
                }
                else
                {
                    pParentCode.Value = parentCode;
                }
                command.Parameters.Add(pParentCode);

                SQLiteParameter pName = command.CreateParameter();
                pName.ParameterName = "@Name";
                pName.Value = name;
                command.Parameters.Add(pName);

                SQLiteParameter pPinyin = command.CreateParameter();
                pPinyin.ParameterName = "@Pinyin";
                pPinyin.Value = pinyin;
                command.Parameters.Add(pPinyin);

                SQLiteParameter pLetter = command.CreateParameter();
                pLetter.ParameterName = "@Letter";
                pLetter.Value = letter;
                command.Parameters.Add(pLetter);

                SQLiteParameter pShortIndex = command.CreateParameter();
                pShortIndex.ParameterName = "@SortIndex";
                pShortIndex.Value = sortIndex;
                command.Parameters.Add(pShortIndex);

                SQLiteParameter pTag = command.CreateParameter();
                pTag.ParameterName = "@Tag";
                if (tag != null)
                {
                    pTag.Value = tag.Value;
                }
                else
                {
                    pTag.Value = DBNull.Value;
                }
                command.Parameters.Add(pTag);

                try
                {
                    command.ExecuteNonQuery();
                    return new ServiceResult();
                }
                catch (Exception ex)
                {
                    return new ServiceResult(1, ex.ToString());
                }
            }
        }

        public ServiceResult CreateMasterDataItem(SQLiteConnection connection, SQLiteTransaction transcation, string masterDataCode, string code, string parentCode, string name, string pinyin, string letter, int sortIndex, int? tag)
        {
            SQLiteCommand command = new SQLiteCommand("insert into [ASMS_MasterDataItem] ([MasterDataCode],[Code],[ParentCode],[Name],[Pinyin],[Letter],[SortIndex],[Tag]) values (@MasterDataCode,@Code,@ParentCode,@Name,@Pinyin,@Letter,@SortIndex,@Tag)", connection, transcation);
            
            SQLiteParameter pMDCode = command.CreateParameter();
            pMDCode.ParameterName = "@MasterDataCode";
            pMDCode.Value = masterDataCode;
            command.Parameters.Add(pMDCode);

            SQLiteParameter pCode = command.CreateParameter();
            pCode.ParameterName = "@Code";
            pCode.Value = code;
            command.Parameters.Add(pCode);

            SQLiteParameter pParentCode = command.CreateParameter();
            pParentCode.ParameterName = "@ParentCode";
            if (string.IsNullOrEmpty(parentCode))
            {
                pParentCode.Value = DBNull.Value;
            }
            else
            {
                pParentCode.Value = parentCode;
            }
            command.Parameters.Add(pParentCode);

            SQLiteParameter pName = command.CreateParameter();
            pName.ParameterName = "@Name";
            pName.Value = name;
            command.Parameters.Add(pName);

            SQLiteParameter pPinyin = command.CreateParameter();
            pPinyin.ParameterName = "@Pinyin";
            pPinyin.Value = pinyin;
            command.Parameters.Add(pPinyin);

            SQLiteParameter pLetter = command.CreateParameter();
            pLetter.ParameterName = "@Letter";
            pLetter.Value = letter;
            command.Parameters.Add(pLetter);

            SQLiteParameter pShortIndex = command.CreateParameter();
            pShortIndex.ParameterName = "@SortIndex";
            pShortIndex.Value = sortIndex;
            command.Parameters.Add(pShortIndex);

            SQLiteParameter pTag = command.CreateParameter();
            pTag.ParameterName = "@Tag";
            if (tag != null)
            {
                pTag.Value = tag.Value;
            }
            else
            {
                pTag.Value = DBNull.Value;
            }
            command.Parameters.Add(pTag);

            try
            {
                command.ExecuteNonQuery();
                return new ServiceResult();
            }
            catch (Exception ex)
            {
                return new ServiceResult(1, ex.ToString());
            }
        }
    
    }
}
